package org.zf.database.server;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.zf.database.consts.FieldType;
import org.zf.database.domain.DBInfo;
import org.zf.database.domain.FieldInfo;
import org.zf.database.domain.TableInfo;
import org.zf.database.exception.NotSupportException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public abstract class AbstractCommonDBServer implements CommonDBServer
{
    @Override
    public List<String> user(DBInfo dbInfo) throws Exception
    {
        try (Connection conn = getConn(dbInfo); ResultSet users = conn.getMetaData().getSchemas())
        {
            Set<String> userSet = new HashSet<>();
            while (users.next())
            {
                while (users.next())
                {
                    userSet.add(users.getString(1));
                }
            }
            return new ArrayList<>(userSet);
        } catch (Exception e)
        {

            log.error("查询异常", e);
        }
        return null;
    }

    @Override
    public List<TableInfo> table(DBInfo dbInfo) throws Exception
    {
        List<TableInfo> tableInfos = new ArrayList<>();
        try (Connection conn = getConn(dbInfo);
             ResultSet tables = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE", "VIEW"})
        )
        {

            while (tables.next())
            {
                String table_schem = tables.getString("TABLE_SCHEM");
                if (StrUtil.isBlank(table_schem))
                    table_schem = tables.getString("TABLE_CAT");
                if (dbInfo.getOwner().equalsIgnoreCase(table_schem))
                {
                    tableInfos.add(new TableInfo()
                    {{
                        setDbInfo(dbInfo);
                        setTableName(tables.getString("TABLE_NAME"));
                        setIsTable(tables.getString("TABLE_TYPE").toUpperCase().contains("TABLE"));
                        setComment(tables.getString("REMARKS"));
                    }});
                }
            }
        } catch (Exception e)
        {
            log.error("查询异常", e);
            tableInfos.clear();
        }
        return tableInfos;
    }

    protected int countTable(TableInfo tableInfo)
    {
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT count(*) FROM {}.{}", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
        )
        {
            if (rs.next())
                return rs.getInt(1);
        } catch (Exception e)
        {
        }
        return 0;
    }

    @Override
    public List<FieldInfo> cols(TableInfo tableInfo) throws Exception
    {
        Set<FieldInfo> fields = new LinkedHashSet<>();
        String catalog = null;
        String schema = tableInfo.getDbInfo().getOwner();
        switch (tableInfo.getDbInfo().getDbType())
        {
            case MYSQL:
                catalog = schema;
                schema = null;
                break;
        }
        try (Connection conn = getConn(tableInfo.getDbInfo());
             ResultSet primaryKeys = conn.getMetaData().getPrimaryKeys(catalog, schema, tableInfo.getTableName());
             ResultSet columns = conn.getMetaData().getColumns(catalog, schema, tableInfo.getTableName(), "%");
        )
        {
            while (columns.next())
            {
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setTableInfo(tableInfo);
                fieldInfo.setFieldName(columns.getString("COLUMN_NAME"));
                fieldInfo.setSourceType(columns.getString("TYPE_NAME"));
                fieldInfo.setLength(columns.getInt("COLUMN_SIZE"));
                fieldInfo.setCommonType(fieldInfo.getLength() > 4000 && convertFieldType(fieldInfo.getSourceType()) == FieldType.VARCHAR ? FieldType.CLOB : convertFieldType(fieldInfo.getSourceType()));
                fieldInfo.setPrecision(columns.getInt("DECIMAL_DIGITS"));
                fieldInfo.setNullable(columns.getString("IS_NULLABLE").equalsIgnoreCase("YES"));
                fieldInfo.setComment(columns.getString("REMARKS"));
                fields.add(fieldInfo);
            }
            while (primaryKeys.next())
            {
                String column_name = primaryKeys.getString("COLUMN_NAME");
                for (FieldInfo field : fields)
                {
                    if (field.getFieldName().equalsIgnoreCase(column_name))
                    {
                        field.setIsPK(true);
                        field.setPkInd(primaryKeys.getInt("KEY_SEQ"));
                    }
                }
            }
        } catch (Exception e)
        {
            log.error("查询异常", e);
            fields.clear();
        }
        return new ArrayList<>(fields);
    }

    @Override
    public String getCreateFieldSQL(List<FieldInfo> fieldInfos) throws NotSupportException
    {
        StringBuilder sb = new StringBuilder();
        for (FieldInfo fieldInfo : fieldInfos)
        {
            String colName = fieldInfo.getFieldName();
            String defaults = fieldInfo.getDefaultStr();
            if (!getDEFAULTMAP().containsKey(fieldInfo.getCommonType()))
                throw new NotSupportException("不支持的字段类型:" + fieldInfo.getCommonType().getType());
            sb.append(StrUtil.format("{} {} {} {} {}", colName, getTypeSql(fieldInfo), fieldInfo.getNullable() ? "NULL" : "NOT NULL", StrUtil.isBlank(defaults) ? " " : " DEFAULT " + defaults, StrUtil.isEmpty(fieldInfo.getComment()) ? " " : StrUtil.format("COMMENT '{}'", fieldInfo.getComment())));
            sb.append(",");
        }
        if (sb.length() > 0)
        {
            return sb.substring(0, sb.length() - 1);
        }
        return sb.toString();
    }

    @Override
    public String addColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        String dbName = fieldInfo.getTableInfo().getDbInfo().getOwner();
        String tableName = fieldInfo.getTableInfo().getTableName();
        String colName = fieldInfo.getFieldName();
        String defaults = fieldInfo.getDefaultStr();
        if (!getDEFAULTMAP().containsKey(fieldInfo.getCommonType()))
            throw new NotSupportException("不支持的字段类型:" + fieldInfo.getCommonType().getType());
        return StrUtil.format("ALTER TABLE {}.{} ADD {} {} {} {} {}", dbName, tableName, colName, getTypeSql(fieldInfo), fieldInfo.getNullable() ? "NULL" : "NOT NULL", StrUtil.isBlank(defaults) ? " " : " DEFAULT " + defaults, StrUtil.isEmpty(fieldInfo.getComment()) ? " " : StrUtil.format("COMMENT '{}'", fieldInfo.getComment()));
    }

    @Override
    public String delColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        return StrUtil.format("ALTER TABLE {}.{} DROP COLUMN {}", fieldInfo.getTableInfo().getDbInfo().getOwner(), fieldInfo.getTableInfo().getTableName(), fieldInfo.getFieldName());
    }

    @Override
    public FieldType convertFieldType(Map<FieldType, List<String>> TYPEMAP, String type) throws NotSupportException
    {
        if (StrUtil.isEmpty(type))
            throw new NotSupportException("不支持空类型");
        int ind = type.indexOf("(");
        if (ind != -1)
            type = type.substring(ind, type.indexOf(")"));
        for (Map.Entry<FieldType, List<String>> entry : TYPEMAP.entrySet())
        {
            if (entry.getValue().contains(type.toUpperCase())) return entry.getKey();
        }
        throw new NotSupportException("不支持的数据类型：" + type);
    }

    abstract public Map<FieldType, String> getDEFAULTMAP();

    protected String getTypeSql(FieldInfo fieldInfo)
    {
        Map<FieldType, String> defaultmap = getDEFAULTMAP();
        String type = defaultmap.get(fieldInfo.getCommonType());
//        if (StrUtil.isNotBlank(fieldInfo.getSourceType())){
//            int i = fieldInfo.getSourceType().indexOf("(");
//            type = i==-1? fieldInfo.getSourceType() : fieldInfo.getSourceType().substring(0,i);
//        }
        return StrUtil.format(Convert.toStr(type, ""), fieldInfo.getLength(), fieldInfo.getPrecision());
    }

    /**
     * 获取字段长度
     *
     * @param charLen
     * @param columnType
     * @return
     */
    public static int getFieldLength(String charLen, String columnType)
    {
        if (charLen != null)
        {
            return Convert.toInt(charLen, 0);
        }

        Pattern p = Pattern.compile("[(]\\d+[)]");
        Matcher m = p.matcher(columnType);
        if (m.find())
        {
            String group = m.group();
            if (group != null)
            {
                group = group.substring(1, group.length() - 1);
            }
            return Convert.toInt(group, 0);
        } else
        {
            return 0;
        }
    }
}
